package com.landis.temple.mobile;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Properties;

import android.database.Cursor;
import android.database.MatrixCursor;
import android.util.Log;


/**
 * Remote MySQL database - all methods are static and not thread safe
 *
 * @author John Landis 
 * @author Anton Kronaj
 * 
 */
public class MySQLAccess
{
    private static final String TAG = "MySQLAccess";
    
    // Reused database elements
    
    private static Connection connect                  = null;
    private static  Properties props                    = null;
    private static PreparedStatement preparedStatement = null;
    private static ResultSet results                   = null;
    
    
    // Database login values for temporary local testing environment
    
    private static final String URL = "10.0.2.2";
    private static final String PORT = "3306";
    private static final String USER = "jalandis";
    private static final String PASS = "ham6ig";
    
    /**
     * Prepares the connection for queries - Completely not thread safe
     */
    private static void setUpConnection()
    {
        try 
        {
            if( connect != null && !connect.isClosed() ) return;
            
            // This will load the MySQL driver, each DB has its own driver    
            Log.d( TAG , "Adding database connector" );
        
            Class.forName("com.mysql.jdbc.Driver");
        
            Log.d( TAG , "Added database connector" );
        
            // Setup the connection with the DB
            props = new Properties();
            props.setProperty("user", USER );
            props.setProperty("password", PASS );
            props.setProperty( "zeroDateTimeBehavior", "convertToNull" );
        
            Log.d( TAG , "Before connect");
            connect = DriverManager.getConnection( "jdbc:mysql://" + URL + ":" + PORT + "/twitter_story" , props );
            Log.d( TAG , "After connect" );
        } 
        catch (ClassNotFoundException e) 
        {
            Log.d( TAG , "ClassNotFoundException : " + e.toString() );
            e.printStackTrace();
        } 
        catch (SQLException e) 
        {
            Log.d( TAG , "SQLException : " + e.toString() );
            e.printStackTrace();
        }
    }
    
    /**
     * Search for author id on author title
     * 
     * @param author = string author name from twitter login
     * @return new author row primary key or -1 if not found
     */
    public static long getAuthorId( String author )
    {
        setUpConnection();
        
        try 
        {
            preparedStatement = connect.prepareStatement("SELECT authors_id FROM authors WHERE authors_name=?");
            preparedStatement.setString( 1 , author );
            
            results = preparedStatement.executeQuery();
            results.first();
            return results.getLong( 1 );
        } 
        catch (SQLException e) 
        {
            Log.d( TAG , "SQLException in getAuthorId: " + e.toString() );
            e.printStackTrace();
        }
        
        return -1;
    }
    
    /**
     * Search for story id by story title
     * 
     * @param story = string story title
     * @return found story primary key or -1 if not found
     */
    public static long getStoryId( String story )
    {
        setUpConnection();
        
        try 
        {
            
            preparedStatement = connect.prepareStatement("SELECT stories_id FROM stories WHERE stories_title=?");
            preparedStatement.setString( 1 , story );
            
            results = preparedStatement.executeQuery();
            results.first();
            return results.getLong( 1 );
        } 
        catch (SQLException e) 
        {
            Log.d( TAG , "SQLException in getStoryId: " + e.toString() );
            e.printStackTrace();
        }
        
        return -1l;
    }
    
    /**
     * Creates a new chapter
     * 
     * @param author - string 
     * @param story - string
     * @param text - string
     * 
     * @return long - chapter primary key or -1 if failed
     */
    public static long addChapter( String author , String story , String text )
    {
        try
        {
            setUpConnection();
            
            // Search for proper author and story id
            long author_id = getAuthorId( author );
            long story_id = getStoryId( story );
            
            preparedStatement = connect.prepareStatement("INSERT INTO chapters( chapters_authors_id , chapters_stories_id , chapters_text ) VALUES ( ? , ? , ?)");
            preparedStatement.setLong( 1  , author_id );
            preparedStatement.setLong( 2 , story_id );
            preparedStatement.setString( 3  , text );
            
            Log.d( TAG , "Before query" );
            return preparedStatement.executeUpdate();
        }
        catch( SQLException e )
        {
            Log.d( TAG , "SQLException in addChapter: " + e.toString() );
            e.printStackTrace();
        }
        finally
        {
            if( connect != null )
            {
                try { connect.close(); } catch( Exception e ) {}
            }
        }
        
        return -1l;
    }
    
    /**
     * Creates a new story
     * 
     * @param author - string
     * @param title - string
     * 
     * @return long - story primary key or -1 if failed
     */
    public static long addStory( String author , String title)
    {
        Log.d( TAG , "addStory( " + author + " , " + title + " )" );
        try
        {
            setUpConnection();
            
            // Search for proper author id
            long author_id = getAuthorId( author );
            
            preparedStatement = connect.prepareStatement("INSERT INTO stories ( stories_authors_id , stories_title ) VALUES ( ? , ? )");
            preparedStatement.setLong( 1  , author_id );
            preparedStatement.setString( 2  , title );
            
            Log.d( TAG , "Before query" );
            return preparedStatement.executeUpdate();
        }
        catch( SQLException e )
        {
            Log.d( TAG , "SQLException : " + e.toString() );
            e.printStackTrace();
        }
        finally
        {
            if( connect != null )
            {
                try { connect.close(); } catch( Exception e ) {}
            }
        }
        
        return -1l;
    }
    
    /**
     * Creates author
     * 
     * @param author_name - string
     * 
     * @return long - author primary key or -1 if failed
     */
    public static long addAuthor( String author_name )
    {
        try
        {
            setUpConnection();
            
            preparedStatement = connect.prepareStatement("INSERT INTO authors ( authors_name ) VALUES ( ? )");
            preparedStatement.setString( 1  , author_name );
            
            return preparedStatement.executeUpdate();            
        }
        catch( SQLException e )
        {
            Log.d( TAG , "SQLException : " + e.toString() );
            e.printStackTrace();
        }
        finally
        {
            if( connect != null )
            {
                try { connect.close(); } catch( Exception e ) {}
            }
        }
        
        return -1l;
    }
    
    /**
     * Search for stories by author
     *     
     * @param string author name from twitter login or other database
     * @return MatrixCursor { long _id , String authors_name , String stories_title , Date stories_date_created }
     */
    public static Cursor searchAuthorsStories( String author )
    {
        try
        {   
            setUpConnection();
            
            Long author_id = getAuthorId( author );
            
            if( author_id < 0 ) return null;
            
            preparedStatement = connect.prepareStatement("SELECT s.stories_id , a.authors_name , s.stories_title , s.stories_date_created " +
            		                                     "FROM stories AS s LEFT JOIN authors AS a " +
            		                                     "ON s.stories_authors_id = a.authors_id " +
            		                                     "WHERE s.stories_authors_id=?");
            preparedStatement.setLong( 1 , author_id );
            
            ResultSet set = preparedStatement.executeQuery();
            
            MatrixCursor curse = new MatrixCursor( new String[] {"_id" , "authors_name" , "stories_title" , "stories_date_created"} );
            while( set.next() )
            {
                curse.addRow(new Object[] { set.getLong("stories_id") , 
                                            set.getString("authors_name") , 
                                            set.getString("stories_title") ,
                                            set.getDate("stories_date_created") });
            }
            return curse;
        }
        catch( SQLException e )
        {
            Log.d( TAG , "SQLException : " + e.toString() );
            e.printStackTrace();
        }
        finally
        {
            if( connect != null )
            {
                try { connect.close(); } catch( Exception e ) {}
            }
        }
        
        return null;
    }
    
    /**
     * Search story details by story title
     *     
     * @param string story title
     * @return MatrixCursor { long _id , String authors_name , String stories_title , Date stories_date_created }
     */
    public static Cursor searchStories( String storyTitle )
    {
        try
        {   
            setUpConnection();
            
            preparedStatement = connect.prepareStatement("SELECT s.stories_id , a.authors_name , s.stories_title , s.stories_date_created " +
                    "FROM stories AS s LEFT JOIN authors AS a " +
                    "ON s.stories_authors_id = a.authors_id " +
                    "WHERE s.stories_title=?");
            preparedStatement.setString( 1 , storyTitle );

            ResultSet set = preparedStatement.executeQuery();

            MatrixCursor curse = new MatrixCursor( new String[] {"_id" , "authors_name" , "stories_title" , "stories_date_created"} );
            while( set.next() )
            {
                curse.addRow(new Object[] { set.getLong("stories_id") , 
                set.getString("authors_name") , 
                set.getString("stories_title") ,
                set.getDate("stories_date_created") });
            }
            return curse;        
        }
        catch( SQLException e )
        {
            Log.d( TAG , "SQLException : " + e.toString() );
            e.printStackTrace();
        }
        finally
        {
            if( connect != null )
            {
                try { connect.close(); } catch( Exception e ) {}
            }
        }
        
        return null;
    }
    
    /**
     * Search for chapters on story title
     *     
     * @param string story title
     * @return MatrixCursor { long _id , long chapters_stories_id , long chapters_authors_id , Date chapters_date_created , String chapters_text }
     */
    public static Cursor getChapters( String story )
    {
        Log.d( TAG , "in getChapters" );
        try
        {   
            setUpConnection();
            
            long story_id = getStoryId( story );
            
            preparedStatement = connect.prepareStatement("SELECT * FROM chapters WHERE chapters_stories_id=?");            
            preparedStatement.setLong( 1 , story_id );
            results = preparedStatement.executeQuery();
            
            MatrixCursor curse = new MatrixCursor( new String[] {"_id" , 
                                                                 "chapters_stories_id" , 
                                                                 "chapters_authors_id" , 
                                                                 "chapters_date_created" ,
                                                                 "chapters_text" } );
            while( results.next() )
            {
                curse.addRow(new Object[] { results.getLong("chapters_id") , 
                                            results.getLong("chapters_stories_id") , 
                                            results.getLong("chapters_authors_id") ,
                                            results.getDate("chapters_date_created") ,
                                            results.getString("chapters_text")});
            }
            return curse;       
        }
        catch( SQLException e )
        {
            Log.d( TAG , "SQLException : " + e.toString() );
            e.printStackTrace();
        }
        finally
        {
            if( connect != null )
            {
                try { connect.close(); } catch( Exception e ) {}
            }
        }
        
        return null;
    }
    
    /**
     * Removes all chapters associated with a story
     * 
     * @param story - string
     */
    public static void removeChapters( String story )
    {
        try
        {   
            setUpConnection();
            
            Log.d( TAG , "in removeChapters : " + story );
            long story_id = getStoryId( story );
            
            preparedStatement = connect.prepareStatement("DELETE FROM chapters WHERE chapters_stories_id=?");            
            preparedStatement.setLong( 1 , story_id );
            
            preparedStatement.executeUpdate();            
        }
        catch( SQLException e )
        {
            Log.d( TAG , "SQLException : " + e.toString() );
            e.printStackTrace();
        }
        finally
        {
            if( connect != null )
            {
                try { connect.close(); } catch( Exception e ) {}
            }
        }
    }
    
    /**
     * Removes a story
     * 
     * @param story - string
     */
    public static void removeStory( String story )
    {
        try
        {   
            setUpConnection();
            
            long story_id = getStoryId( story );
            
            preparedStatement = connect.prepareStatement("DELETE FROM stories WHERE stories_id=?");            
            preparedStatement.setLong( 1 , story_id );
            
            preparedStatement.executeUpdate();            
        }
        catch( SQLException e )
        {
            Log.d( TAG , "SQLException in removeStory: " + e.toString() );
            e.printStackTrace();
        }
        finally
        {
            if( connect != null )
            {
                try { connect.close(); } catch( Exception e ) {}
            }
        }
    }
}
